package vn.com.demand.lux.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import vn.com.demand.lux.criteria.WarrantyExpenseAccrualCriteria;
import vn.com.demand.lux.model.WarrantyExpenseAccrualBean;
import vn.com.demand.util.DpUtils;

/**
 * @author Huynh Tan Tai <httcntt@yahoo.com>
 *
 */
public class WarrantyExpenseAccrualDao {
	private static SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
	
	public static List<WarrantyExpenseAccrualBean> searchWarrantyExpenseAccruals(WarrantyExpenseAccrualCriteria criteria){
		if (null != criteria.getFromDate() && null != criteria.getToDate()) {
			Connection connection = null; 
			try {
			connection = DpUtils.getConnection();
			String sql = "SELECT TOP 100 PERCENT ContractNumber, MAX(Type) AS Type, MAX(CostCenter) AS CostCenter, SUM(NetSales) AS NetSales, " +
				  " SUM(AccumulatedWarrantyExpense) AS AccumulatedWarrantyExpense, CASE WHEN MIN(WarrantyExpense) = 0 " +
				  " THEN round(SUM(NetSales) * _percentParam / 100, 2) ELSE MIN(WarrantyExpense) END AS WarrantyExpense" +
				  " FROM (SELECT g.project AS ContractNumber, p.CostCenter, k.oms25_0 AS Type, CASE WHEN LEFT(ltrim(g.reknr), 3) " +
				  "  = '511' THEN - g.bdr_hfl ELSE 0 END AS NetSales, CASE WHEN Ltrim(ltrim(g.reknr))" +
				  "  = '_expenseLedgerParam' THEN g.bdr_hfl ELSE 0 END AS AccumulatedWarrantyExpense, CASE WHEN k.UserNumber_01 IS NOT NULL" +
				  " THEN k.UserNumber_01 ELSE 0 END AS WarrantyExpense FROM dbo.gbkmut g INNER JOIN dbo.PRProject p ON g.project = p.ProjectNr" +
				  " LEFT OUTER JOIN dbo.kstdr k ON p.Costunit = k.kstdrcode WHERE (p.YesNoField1 + YesNoField2 > 0) AND (p.Type IN ('I')) " +
				  " AND (NOT (g.transtype IN ('V', 'B'))) AND (p.InitialStartDate BETWEEN CONVERT(DATETIME, '_fromDateParam', 102)" +
				  " AND CONVERT(DATETIME, '_toDateParam', 102))) g GROUP BY ContractNumber HAVING SUM(AccumulatedWarrantyExpense) = 0 " +
				  " AND SUM(NetSales) > 0 ORDER BY ContractNumber";
			sql = sql.replaceAll("_expenseLedgerParam", criteria.getExpenseLedger());
			sql = sql.replaceAll("_percentParam", criteria.getMachineAccrualPercentage());
			sql = sql.replaceAll("_fromDateParam", df.format(criteria.getFromDate()));
			sql = sql.replaceAll("_toDateParam", df.format(criteria.getToDate()));
//			System.out.println("SQL: " +sql);
			ResultSetHandler handler = new BeanListHandler(WarrantyExpenseAccrualBean.class);
			QueryRunner runner = new QueryRunner();
			return (List<WarrantyExpenseAccrualBean>) runner.query(connection, sql, handler);
			} catch (SQLException e) {
				e.printStackTrace();
				DpUtils.rollback(connection);
			} finally {
				DpUtils.releaseConnection(connection);
			}
		}
		return null;
	}
}
